﻿IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[esf_sso].[VPerson]'))
	DROP VIEW [esf_sso].[VPerson]
GO

-- SELECT * FROM [esf_sso].[VPerson]

CREATE VIEW [esf_sso].[VPerson] AS

	SELECT
		P.[ID]
		,P.[FirstName]
		,P.[SecondName]
		,P.[SurName]
		,P.[LastName]
		,P.[BirthDate]
		,P.[IdentificationNumber]
		,P.[Email]
		,P.Profession
		,P.Position
		,FirstName + COALESCE (' ' + SecondName, '') + COALESCE (' ' + SurName, '') + COALESCE (' ' + LastName, '') AS cal_FullName
			
		,G.ID						AS Gen_ID
		,G.Name						AS Gen_Name
		
		,IT.ID						AS IT_ID
		,IT.Code					AS IT_Code
		,IT.Name					AS IT_Name
		
		,E.ID						AS Ent_ID
		,ISNULL (E.Name, P.OtherEnterprise) AS Ent_Name
		
		,UML.*
	FROM 
		[esf_sso].Person P
	LEFT JOIN
		[esf_sso].BasicReference G ON G.ID = P.GenderID
	INNER JOIN
		[esf_sso].BasicReference IT ON IT.ID = P.IdentificationTypeID
	LEFT JOIN
		[esf_sso].BasicReference E ON E.ID = P.EnterpriseID
	LEFT JOIN
		(SELECT 
			TOP 1 
			L.CityName			AS MainLoc_CityName
			,L.PhoneNumber		AS MainLoc_PhoneNumber
			,L.CellPhoneNumber	AS MainLoc_CellPhoneNumber
			,L.[Address]		AS MainLoc_Address
			,PL.PersonID
		FROM 
			esf_sso.Person_Location PL
		INNER JOIN
			esf_sso.Location L ON L.ID = PL.LocationID
		WHERE
			L.Name = 'Principal') UML ON UML.PersonID = P.ID
